﻿using System;
using System.Collections.Generic;
using System.Text;
using DotNet.Model;
using DotNet.Common;
using DotNet.Data.Factory;
using System.Collections;
using System.Data;
using System.Data.SqlClient;
namespace DotNet.Data
{
    public class NewsDal
    {
        IDBHelp db = DBHelp.Create();
        Hashtable htSyn = Hashtable.Synchronized(new Hashtable());
        StringBuilder strSql = new StringBuilder();
        #region 是否存在该记录
        /// <summary>
        /// 是否存在该记录
        /// </summary>
        public bool Exists(int newId)
        {
            htSyn.Clear();
            strSql.Remove(0, strSql.Length);
            strSql.Append("select count(1) from new where newId=@newId");
            htSyn.Add("newId", newId);
            return db.IsExists(strSql.ToString(), htSyn);
        }
        #endregion

        #region 获得新闻列表
        /// <summary>
        /// 获得新闻列表
        /// </summary>
        /// <returns></returns>
        public IList<News> GetModel()
        {
            htSyn.Clear();
            strSql.Remove(0, strSql.Length);
            strSql.Append("select newId,newName,newClassId,newInfo,newSDate,newEDate,nTitle,nKey,nDescription,nButtom from News ");
            return getProductsBySql(strSql.ToString());
        }
        #endregion

        #region 根据新闻号查询实体
        /// <summary>
        /// 根据新闻号查询实体
        /// </summary>
        /// <param name="newId"></param>
        /// <returns></returns>
        public News GetModel(int newId)
        {
            htSyn.Clear();
            strSql.Remove(0, strSql.Length);
			strSql.Append("select newId,newName,newClassId,newInfo,newSDate,newEDate,nTitle,nKey,nDescription,nButtom from News ");
			strSql.Append(" where newId=@newId ");
            htSyn.Add("@newId", newId);
            DataTable dt = db.GetDataTable(strSql.ToString(), htSyn); 
            News news =new News();
            if (dt.Rows.Count>0)
            {
                news.newId = Convert.ToInt32(dt.Rows[0]["newId"]);
                news.newName = dt.Rows[0]["newName"].ToString();
                news.newClassId = Convert.ToInt32(dt.Rows[0]["newClassId"]);
                news.newInfo = dt.Rows[0]["newInfo"].ToString();
                news.newSDate = Convert.ToDateTime(dt.Rows[0]["newSDate"]);
                news.newEDate = Convert.ToDateTime(dt.Rows[0]["newEDate"]);
                news.nTitle = dt.Rows[0]["nTitle"].ToString();
                news.nKey = Convert.ToString(dt.Rows[0]["nKey"]);
                news.nDescription = dt.Rows[0]["nDescription"].ToString();
                news.nButtom = dt.Rows[0]["nButtom"].ToString();
            }   
            return news;
        }
        #endregion

        #region 修改新闻信息
        /// <summary>
        /// 修改新闻信息
        /// </summary>
        /// <param name="news"></param>
        /// <returns></returns>
        public bool Update(News news)
        {
            htSyn.Clear();
            strSql.Remove(0, strSql.Length);
			strSql.Append("update News set ");
			strSql.Append("newName=@newName,");
			strSql.Append("newClassId=@newClassId,");
			strSql.Append("newInfo=@newInfo,");
			strSql.Append("newSDate=@newSDate,");
			strSql.Append("newEDate=@newEDate,");
			strSql.Append("nTitle=@nTitle,");
			strSql.Append("nKey=@nKey,");
			strSql.Append("nDescription=@nDescription,");
			strSql.Append("nButtom=@nButtom");
			strSql.Append(" where newId=@newId ");
            htSyn.Add("@newId",news.newId);
            htSyn.Add("@newName",news.newName);
            htSyn.Add("@newClassId",news.newClassId);
            htSyn.Add("@newInfo",news.newInfo);
            htSyn.Add("@newSDate",news.newSDate);
            htSyn.Add("@newEDate",news.newEDate);
            htSyn.Add("@nTitle",news.nTitle);
            htSyn.Add("@nKey",news.nKey);
            htSyn.Add("@nDescription",news.nDescription);
            htSyn.Add("@nButtom",news.nButtom);
            if (db.ExecuteNonQuery(strSql.ToString(), htSyn) > 0)
            {
                return true;
            }
            return false;
        }
        #endregion

        #region 删除新闻根据新闻号
        /// <summary>
        /// 删除新闻根据新闻号
        /// </summary>
        /// <param name="newId"></param>
        /// <returns></returns>
        public bool Delete(int newId)
        {
            htSyn.Clear();
            strSql.Remove(0, strSql.Length);
            strSql.Append("delete from News where newId=@newId");
            htSyn.Add("newId", newId);
            if (db.ExecuteNonQuery(strSql.ToString(), htSyn) > 0)
            {
                return true;
            }
            return false;
        }
        #endregion

        #region 添加新闻
        /// <summary>
        /// 添加新闻
        /// </summary>
        /// <param name="news"></param>
        /// <returns></returns>
        public bool Insert(News news)
        {
            htSyn.Clear();
            strSql.Remove(0, strSql.Length);
            strSql.Append("insert into News([newName],[newClassId],[newInfo],[newSDate],[newEDate],[nTitle],[nKey],[nDescription],[nButtom]) values (@newName,@newClassId,@newInfo,@newSDate,@newEDate,@nTitle,@nKey,@nDescription,@nButtom)");
            htSyn.Add("@newName",news.newName);
            htSyn.Add("@newClassId",news.newClassId);
            htSyn.Add("@newInfo",news.newInfo);
            htSyn.Add("@newSDate",news.newSDate);
            htSyn.Add("@newEDate",news.newEDate);
            htSyn.Add("@nTitle",news.nTitle);
            htSyn.Add("@nKey",news.nKey);
            htSyn.Add("@nDescription",news.nDescription);
            htSyn.Add("@nButtom",news.nButtom);
            if (db.ExecuteNonQuery(strSql.ToString(), htSyn) > 0)
            {
                return true;
            }
            return false;
        }
        #endregion

        #region 根据SQL语句获取集合
        /// <summary>
        ///根据SQL语句获取集合
        /// </summary>
        public IList<News> getProductsBySql(string strSql)
        {
            IList<News> list = new List<News>();
            DataTable dt = db.GetDataTable(strSql);
            if (dt.Rows.Count > 0)
            {
                foreach (DataRow dr in dt.Rows)
                {
                    News news = new News();
                    news.newId = Convert.ToInt32(dr["newId"]);
                    news.newName = dr["newName"].ToString();
                    news.newClassId = Convert.ToInt32(dr["newClassId"]);
                    news.newInfo = dr["newInfo"].ToString();
                    news.newSDate = Convert.ToDateTime(dr["newSDate"]);
                    news.newEDate = Convert.ToDateTime(dr["newEDate"]);
                    news.nTitle = dr["nTitle"].ToString();
                    news.nKey = Convert.ToString(dr["nKey"]);
                    news.nDescription = dr["nDescription"].ToString();
                    news.nButtom = dr["nButtom"].ToString();
                    list.Add(news);
                }
            }
            return list;
        }
        #endregion

#region 新闻分页---郑同飞
        #region 新闻分页---新闻id，新闻关键字
        /// <summary>
        /// 新闻分页---新闻id，新闻关键字
        /// </summary>
        /// <param name="pageIndex"></param>
        /// <param name="pageSize"></param>
        /// <param name="classid">新闻id</param>
        /// <param name="key">新闻关键字</param>
        /// <returns></returns>
        public DataTable pageingForNews(int pageIndex,int pageSize,int classid,string key)
        {
            return db.GetMPageDataTable(pageIndex, pageSize, "newId,newName,newClassId,newInfo,newSDate,newEDate,nTitle,nKey,nDescription,nButtom", "News", "and newClassId='" + classid + "' and newName like '%" + key + "%'", "newEDate ", true);
        }
        #endregion

        #region 新闻分页---新闻id
        /// <summary>
        /// 新闻分页---新闻id
        /// </summary>
        /// <param name="pageIndex"></param>
        /// <param name="pageSize"></param>
        /// <param name="classid">新闻id</param>
        /// <returns></returns>
        public DataTable pageingForNews(int pageIndex, int pageSize, int classid)
        {
            return db.GetMPageDataTable(pageIndex, pageSize, "newId,newName,newClassId,newInfo,newSDate,newEDate,nTitle,nKey,nDescription,nButtom", "News", "and newClassId=" + classid, "newEDate ", true);
        }
        #endregion

        #region 新闻分页---新闻关键字
        /// <summary>
        /// 新闻分页---新闻关键字
        /// </summary>
        /// <param name="pageIndex"></param>
        /// <param name="pageSize"></param>
        /// <param name="key"></param>
        /// <returns></returns>       
        public DataTable pageingForNews(int pageIndex, int pageSize, string key)
        {
            return db.GetMPageDataTable(pageIndex, pageSize, "newId,newName,newClassId,newInfo,newSDate,newEDate,nTitle,nKey,nDescription,nButtom", "News", "and newName like '%" + key + "%'", "newEDate ", true);

        }
        #endregion

        #region 新闻分页
        /// <summary>
        /// 新闻分页
        /// </summary>
        /// <param name="pageIndex"></param>
        /// <param name="pageSize"></param>
        /// <returns></returns>       
        public DataTable pageingForNews(int pageIndex,int pageSize) 
        {
            return db.GetMPageDataTable(pageIndex, pageSize, "newId,newName,newClassId,newInfo,newSDate,newEDate,nTitle,nKey,nDescription,nButtom", "News", "", "newEDate", true);
        }
        #endregion
 #endregion

#region 获得新闻数，用于分页按钮---郑同飞
        #region 获得新闻数，新闻类别，关键字
        /// <summary>
        /// 获得新闻数，新闻类别，关键字
        /// </summary>
        /// <param name="classid">新闻类别</param>
        /// <param name="where">关键字</param>
        /// <returns></returns>
        public int getRecordCount(int classid,string where) 
        {
            htSyn.Clear();
            strSql.Remove(0, strSql.Length);
            object objRecordCount = null;
            int i = 0;
            strSql.Append("select count(1) from News where newClassId=@classid and newName like @where");
            htSyn.Add("@classid", classid);
            htSyn.Add("@where", "%" + where + "%");
            objRecordCount = db.ExecuteScalar(strSql.ToString(), htSyn);                       
            if (objRecordCount != null)
            {
                i = Convert.ToInt32(objRecordCount);
            }
            return i;
        }
        #endregion

        #region 获得新闻数，新闻类别
        /// <summary>
        /// 获得新闻数，新闻类别
        /// </summary>
        /// <param name="classid">新闻类别</param>
        /// <returns></returns>
        public int getRecordCount(int classid)
        {
            htSyn.Clear();
            strSql.Remove(0, strSql.Length);
            object objRecordCount = null;
            int i = 0;
            strSql.Append("select count(1) from News where newClassId=@classid");
            htSyn.Add("@classid",classid);
            objRecordCount = db.ExecuteScalar(strSql.ToString(),htSyn);
            if (objRecordCount != null)
            {
            i = Convert.ToInt32(objRecordCount);
            }
            return i;
        }
        #endregion

        #region 获得新闻数，关键字
        /// <summary>
        /// 获得新闻数，关键字        
        /// </summary>
        /// <param name="where">关键字</param>
        /// <returns></returns>
        public int getRecordCount(string where)
        {
            htSyn.Clear();
            strSql.Remove(0, strSql.Length);
            object objRecordCount = null;
            int i = 0;
            strSql.Append("select count(1) from News where newName like @where");
            htSyn.Add("@where", "%"+where+"%");
            objRecordCount = db.ExecuteScalar(strSql.ToString(), htSyn);
            if (objRecordCount != null)
            {
            i = Convert.ToInt32(objRecordCount);
            }
            return i;
        }
        #endregion

        #region 获得新闻数
        /// <summary>
        /// 获得新闻数
        /// </summary>
        /// <returns></returns>
        public int getRecordCount()
        {
            htSyn.Clear();
            strSql.Remove(0, strSql.Length);
            object objRecordCount = null;
            int i = 0;
            objRecordCount = db.ExecuteScalar("select count(1) from News");
            if (objRecordCount != null)
            {
            i = Convert.ToInt32(objRecordCount);
            }
            return i;
        }  
        #endregion
#endregion

        #region 根据新闻编号批量删除新闻---郑同飞
        /// <summary>
        /// 根据新闻编号批量删除新闻---郑同飞
        /// </summary>
        /// <param name="nid"></param>
        /// <returns></returns>
        public bool Delete(string[] nid) 
        {
            htSyn.Clear();
            strSql.Append("delete from News where");
            foreach (string id in nid)
            {
                strSql.AppendFormat(" newId={0} or",id);
            }
            strSql = strSql.Remove(strSql.Length - 1,1);
            strSql = strSql.Remove(strSql.Length - 1, 1);
            if (db.ExecuteNonQuery(strSql.ToString())>0)
            {
                return true;
            }
            return false; 
        }
        #endregion

        #region 批量移动新闻的类别---郑同飞
        /// <summary>
        /// 批量移动新闻的类别---郑同飞
        /// </summary>
        /// <param name="nid"></param>
        /// <param name="classId"></param>
        /// <returns></returns>
        public bool Update(string[] nid, int classId)
        {
            strSql.Append("Update News set");
            strSql.AppendFormat(" newClassId={0}", classId);
            strSql.Append(" where ");
            foreach (string  id in nid)
            {
                strSql.AppendFormat(" newId={0}  or",id);
            }
            strSql = strSql.Remove(strSql.Length - 1, 1);
            strSql = strSql.Remove(strSql.Length - 1, 1);
            if (db.ExecuteNonQuery(strSql.ToString())>0)
            {
                return true;
            }
            return false;
        }
        #endregion

        #region 修改新闻的类别--郑同飞
        /// <summary>
        /// 修改新闻的类别--郑同飞
        /// </summary>
        /// <param name="news"></param>
        /// <returns></returns>
        public bool updateClass(News news) 
        {
            strSql.Remove(0, strSql.Length);
            htSyn.Clear();
            strSql.Append("update News set ");
            strSql.Append("newClassId=@newclassid");
            strSql.Append(" where newId=@newid");
            htSyn.Add("@newid",news.newId);
            htSyn.Add("@newclassid",news.newClassId);       
            if (db.ExecuteNonQuery(strSql.ToString(),htSyn)>0)  
            {
        		 return true;
            }
            return false;

     }

        #endregion

        #region 查询每个类别下的产品数量---郑同飞
        /// <summary>
        /// 查询每个类别下的产品数量---郑同飞
        /// </summary>
        /// <param name="ncId">新闻类别</param>
        /// <returns></returns>
        public int countByClass(int ncId)
        {
            htSyn.Clear();
            strSql.Remove(0, strSql.Length);
            strSql.Append("select count(newId) from News where newClassId=@ncid");
            htSyn.Add("@ncid", ncId);
            return db.GetRecordCount(strSql.ToString(), htSyn);
        }
     #endregion
    }
}
